﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Reflection;
using Microsoft.AspNetCore.Http;
using System.Globalization;
using System.Linq;
using NetCore.ORM;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;

namespace NetCore.BLL
{
    public class OfficeHelper
    {
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="tableTitle"></param>
        /// <returns></returns>
        public static byte[] OutputExcel(DataTable dataTable, string[] tableTitle)
        {
            IWorkbook workbook = new XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet");
            IRow Title = null;
            IRow rows = null;
            Title = sheet.CreateRow(0);
            for (int k =0; k < tableTitle.Length ; k++)
            {
                Title.CreateCell(k).SetCellValue(tableTitle[k]);
            }
            for (int i = 1; i <= dataTable.Rows.Count; i++)
            {
                //创建表头
                
                rows = sheet.CreateRow(i);
                for (int j =0; j < tableTitle.Length; j++)
                {
                    // rows.CreateCell(0).SetCellValue(i);
                    var value = dataTable.Rows[i - 1][j];
                    rows.CreateCell(j).SetCellValue((value==null?"": value.ToString()));
                }
            }

            byte[] buffer = new byte[1024 *5];
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
                ms.Close();
            }
            return buffer;
        }

        /// <summary>
        /// 导出文件
        /// </summary>
        /// <param name="entitys"></param>
        /// <param name="title"></param>
        /// <returns></returns>
        public static byte[] OutputExcel<T>(List<T> entitys, string[] title)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            IRow Title = null;
            IRow rows = null;
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();
            for (int i = 0; i <= entitys.Count; i++)
            {
                if (i == 0)
                {
                    Title = sheet.CreateRow(0);
                    for (int k = 1; k < title.Length + 1; k++)
                    {
                        Title.CreateCell(0).SetCellValue("序号");
                        Title.CreateCell(k).SetCellValue(title[k - 1]);
                    }

                    continue;
                }
                else
                {
                    rows = sheet.CreateRow(i);
                    object entity = entitys[i - 1];
                    for (int j = 1; j <= entityProperties.Length; j++)
                    {
                        object[] entityValues = new object[entityProperties.Length];
                        entityValues[j - 1] = entityProperties[j - 1].GetValue(entity);
                        rows.CreateCell(0).SetCellValue(i);
                        rows.CreateCell(j).SetCellValue(entityValues[j - 1].ToString());
                    }
                }
            }
            byte[] buffer = new byte[1024 * 5];
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
                ms.Close();
            }

            return buffer;
        }


        public static byte[] OutputExcel(List<Dictionary<string, object>> entities, List<string> title,List<string> columns)
        {
            if (entities == null || entities.Count <= 0)
            {
                return null;
            }
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            IRow Title = null;
            IRow rows = null;
            Title = sheet.CreateRow(0);
            int index = 0;
            foreach (var item in title)
            {
                Title.CreateCell(index).SetCellValue(item);
                index++;
            }
            for (int i = 1; i <= entities.Count; i++)
            {
                rows = sheet.CreateRow(i);
                var entity = entities[i - 1];
                index = 0;
                foreach (var item in entity)
                {
                    if (columns.Contains(item.Key))
                    {
                        rows.CreateCell(index).SetCellValue((item.Value != null ? item.Value.ToString() : ""));
                        index++;
                    }
                   
                }
            }
            byte[] buffer = new byte[1024 *5];
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.ToArray();
                ms.Close();
            }

            return buffer;
        }

        /// <summary>
        /// 生成导出模板
        /// </summary>
        /// <param name="lstTemplate"></param>
        /// <returns></returns>
        public static byte[] OutputExcelTemplate(List<ExcelTemplateEntity> lstTemplate)
        {
            if (lstTemplate == null || lstTemplate.Count <= 0)
            {
                return null;
            }
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            IRow Title = null;
            Title = sheet.CreateRow(0);
            int index = 0;
            foreach (var item in lstTemplate)
            {
                Title.CreateCell(index).SetCellValue((item.IsRequired? "*":"")+item.Name+"("+item.FieldName+")");
                index++;
            }
            byte[] buffer = new byte[1024 * 5];
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
                ms.Close();
            }

            return buffer;
        }


        public static byte[] OutputExcel<T>(List<T> entities)
        {
            if (entities == null || entities.Count <= 0)
            {
                return null;
            }
            DbFactoryHelper dbFactory = new DbFactoryHelper(entities[0].GetType());
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            IRow Title = null;
            IRow rows = null;
            Type entityType = entities[0].GetType();
            Title = sheet.CreateRow(0);
            int index = 0;
            foreach (var item in dbFactory.EntityMap.DbFields)
            {
                if (item.IsEnabled && !item.Identifier && (item.IsEditVisible || item.IsGridVisible))
                {
                    Title.CreateCell(index).SetCellValue(item.Name);
                    index++;
                }

            }
            for (int i = 1; i <= entities.Count; i++)
            {
                rows = sheet.CreateRow(i);
                var entity = entities[i - 1];
                for (int j = 0; j < dbFactory.EntityMap.DbFields.Count; j++)
                {
                    var field = dbFactory.EntityMap.DbFields[j];
                    if (field.IsEnabled && !field.Identifier && (field.IsEditVisible || field.IsGridVisible))
                    {
                        var properties = dbFactory.EntityMap.EntityFields.Where(x => x.Name.Equals(field.Name)).FirstOrDefault();
                        if (properties != null)
                        {
                            //rows.CreateCell(0).SetCellValue(i);
                            var value = properties.GetValue(entity);
                            rows.CreateCell(j).SetCellValue((value != null ? value.ToString() : ""));
                        }
                    }
                }
            }
            byte[] buffer = new byte[1024 *5];
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
                ms.Close();
            }

            return buffer;
        }
        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="file">导入文件</param>
        /// <returns>List<T></returns>
        public static List<T> ImportExcel<T>(IFormFile file)where T:class,new()
        {
            List<T> list = new List<T> { };
            using (MemoryStream ms = new MemoryStream())
            {
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook workbook = null;
                if (file.FileName.ToLower().Contains(".xlsx"))
                {
                    workbook = new XSSFWorkbook(ms);
                }
                else
                {
                    workbook = new HSSFWorkbook(ms);
                }
                ISheet sheet = workbook.GetSheetAt(0);
                IRow cellNum = sheet.GetRow(0);
             
                int num = cellNum.LastCellNum;
                var obj = new T();
                PropertyInfo[] propertys = null;
                List<DbColumnAttribute> dbFields = null;
                if (obj is Dictionary<string, object>)
                {

                }
                else if (obj is EntityBase)
                {
                    DbFactoryHelper dbFactory = new DbFactoryHelper(typeof(T));
                    if (dbFactory != null)
                    {
                        dbFields = dbFactory.EntityMap.DbFields;
                        propertys = dbFactory.EntityMap.EntityFields.ToArray();
                    }
                }
                else
                {
                    propertys = typeof(T).GetProperties();
                }
                Regex regex = new Regex("^.+\\(([a-zA-Z_]+)\\)$");//包含必填 *姓名(Name)
             
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    obj = new T();
                    if (obj is Dictionary<string, object>)
                    {
                        var dic = obj as Dictionary<string, object>;
                        for (int j = 0; j < num; j++)
                        {
                            if (row.GetCell(j) == null)
                                continue;
                            string value = row.GetCell(j).ToString().Trim();
                            string columnName = cellNum.GetCell(j).ToString().Trim();
                            if (regex.IsMatch(columnName))
                            {
                                dic.Add(regex.Match(columnName).Groups[1].Value, value);
                            }
                            else
                            {
                                dic.Add(columnName.Replace("*",""), value);
                            }
                        }
                    }
                    else if (obj is Dictionary<string, string>)
                    {
                        var dic = obj as Dictionary<string, string>;
                        for (int j = 0; j < num; j++)
                        {
                            if (row.GetCell(j) == null)
                                continue;
                            string value = row.GetCell(j).ToString().Trim();
                            string columnName = cellNum.GetCell(j).ToString().Trim();
                            if (regex.IsMatch(columnName))
                            {
                                dic.Add(regex.Match(columnName).Groups[1].Value, value);
                            }
                            else
                            {
                                dic.Add(columnName.Replace("*", ""), value);
                            }
                        }
                    }
                    else
                    {
                        for (int j = 0; j < num; j++)
                        {
                            string value = row.GetCell(j).ToString().Trim();
                            if (obj is EntityBase && dbFields != null)
                            {
                                var field = dbFields.Where(x => x.Name == cellNum.GetCell(j).ToString() || x.LocalName == cellNum.GetCell(j).ToString()).FirstOrDefault();
                                if (field != null)
                                {
                                    var property = propertys.Where(x => x.Name == field.Name).FirstOrDefault();
                                    if (property != null)
                                    {
                                        Type fieldtype = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                        property.SetValue(obj, Convert.ChangeType(value, property.PropertyType), null);
                                    }
                                }
                            }
                            else
                            {
                                if (propertys != null)
                                {
                                    var property = propertys.Where(x => x.Name == cellNum.GetCell(j).ToString()).FirstOrDefault();
                                    if (property != null)
                                    {
                                        Type fieldtype = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                        property.SetValue(obj, Convert.ChangeType(value, property.PropertyType), null);
                                    }
                                }
                            }
                        }
                    }
                     
                    list.Add(obj);
                }
            }
            return list;
        }


    }

    /// <summary>
    /// 生成导出模板对象
    /// </summary>
    public class ExcelTemplateEntity
    {
        /// <summary>
        /// 对应字段名
        /// </summary>
        public string FieldName { get; set; }

        /// <summary>
        /// 描述
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 是否必填
        /// </summary>
        public bool IsRequired { get; set; }
    }
}
